#include <mysql/mysql.h>
#include <string>
#include <vector>
#include <memory>
#include <stdexcept>
#include <mutex>
#include <cstring>
#include <iostream>

class MySQLHelper
{
public:
    // 获取单例实例
    static MySQLHelper &getInstance()
    {
        static MySQLHelper instance;
        return instance;
    }

    // 初始化连接
    void initialize(const std::string &host, const std::string &user,
                    const std::string &password, const std::string &database,
                    unsigned int port = 3306)
    {
        std::lock_guard<std::mutex> lock(mutex_);

        if (connected_)
        {
            return;
        }

        mysql_ = mysql_init(nullptr);
        if (!mysql_)
        {
            throw std::runtime_error("MySQL initialization failed");
        }

        // 设置自动重连
        bool reconnect = 1;
        mysql_options(mysql_, MYSQL_OPT_RECONNECT, &reconnect);

        // 设置连接超时
        unsigned int timeout = 5;
        mysql_options(mysql_, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);

        if (!mysql_real_connect(mysql_, host.c_str(), user.c_str(),
                                password.c_str(), database.c_str(),
                                port, nullptr, 0))
        {
            std::string error = mysql_error(mysql_);
            mysql_close(mysql_);
            mysql_ = nullptr;
            throw std::runtime_error("Connection failed: " + error);
        }

        connected_ = true;
    }

    // 执行SQL语句
    void execute(const std::string &sql)
    {
        std::lock_guard<std::mutex> lock(mutex_);
        checkConnection();

        if (mysql_real_query(mysql_, sql.c_str(), sql.length()) != 0)
        {
            throw std::runtime_error("Query failed: " + std::string(mysql_error(mysql_)));
        }
    }

    // 执行预处理语句
    void executePreparedStatement(const std::string &sql, MYSQL_BIND *bind, unsigned int param_count)
    {
        std::lock_guard<std::mutex> lock(mutex_);
        checkConnection();

        MYSQL_STMT *stmt = mysql_stmt_init(mysql_);
        if (!stmt)
        {
            throw std::runtime_error("Statement initialization failed");
        }

        try
        {
            if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length()) != 0)
            {
                throw std::runtime_error("Prepare failed: " + std::string(mysql_stmt_error(stmt)));
            }

            if (param_count > 0 && mysql_stmt_bind_param(stmt, bind) != 0)
            {
                throw std::runtime_error("Bind param failed: " + std::string(mysql_stmt_error(stmt)));
            }

            if (mysql_stmt_execute(stmt) != 0)
            {
                throw std::runtime_error("Execute failed: " + std::string(mysql_stmt_error(stmt)));
            }

            mysql_stmt_close(stmt);
        }
        catch (...)
        {
            mysql_stmt_close(stmt);
            throw;
        }
    }

    // 查询数据
    std::vector<std::vector<std::string>> query(const std::string &sql)
    {
        std::lock_guard<std::mutex> lock(mutex_);
        checkConnection();

        if (mysql_real_query(mysql_, sql.c_str(), sql.length()) != 0)
        {
            throw std::runtime_error("Query failed: " + std::string(mysql_error(mysql_)));
        }

        MYSQL_RES *result = mysql_store_result(mysql_);
        if (!result)
        {
            throw std::runtime_error("Store result failed: " + std::string(mysql_error(mysql_)));
        }

        std::vector<std::vector<std::string>> rows;
        MYSQL_ROW row;
        unsigned int num_fields = mysql_num_fields(result);
        MYSQL_FIELD *fields = mysql_fetch_fields(result);

        // 添加表头
        std::vector<std::string> header;
        for (unsigned int i = 0; i < num_fields; i++)
        {
            header.push_back(fields[i].name);
        }
        rows.push_back(header);

        // 添加数据行
        while ((row = mysql_fetch_row(result)))
        {
            std::vector<std::string> current_row;
            for (unsigned int i = 0; i < num_fields; i++)
            {
                current_row.push_back(row[i] ? row[i] : "NULL");
            }
            rows.push_back(current_row);
        }

        mysql_free_result(result);
        return rows;
    }

    // 使用预处理语句查询
    std::vector<std::vector<std::string>> queryPreparedStatement(const std::string &sql,
                                                                 MYSQL_BIND *bind,
                                                                 unsigned int param_count)
    {
        std::lock_guard<std::mutex> lock(mutex_);
        checkConnection();

        MYSQL_STMT *stmt = mysql_stmt_init(mysql_);
        if (!stmt)
        {
            throw std::runtime_error("Statement initialization failed");
        }

        try
        {
            if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length()) != 0)
            {
                throw std::runtime_error("Prepare failed: " + std::string(mysql_stmt_error(stmt)));
            }

            if (param_count > 0 && mysql_stmt_bind_param(stmt, bind) != 0)
            {
                throw std::runtime_error("Bind param failed: " + std::string(mysql_stmt_error(stmt)));
            }

            if (mysql_stmt_execute(stmt) != 0)
            {
                throw std::runtime_error("Execute failed: " + std::string(mysql_stmt_error(stmt)));
            }

            MYSQL_RES *result = mysql_stmt_result_metadata(stmt);
            if (!result)
            {
                throw std::runtime_error("Get metadata failed: " + std::string(mysql_stmt_error(stmt)));
            }

            std::vector<std::vector<std::string>> rows;
            unsigned int num_fields = mysql_num_fields(result);
            MYSQL_FIELD *fields = mysql_fetch_fields(result);

            // 添加表头
            std::vector<std::string> header;
            for (unsigned int i = 0; i < num_fields; i++)
            {
                header.push_back(fields[i].name);
            }
            rows.push_back(header);

            // 绑定结果
            std::vector<char *> buffers(num_fields);
            std::vector<unsigned long> lengths(num_fields);
            bool *is_null = new bool[num_fields];

            std::vector<MYSQL_BIND> result_bind(num_fields);

            for (unsigned int i = 0; i < num_fields; i++)
            {
                buffers[i] = new char[fields[i].length + 1];
                memset(&result_bind[i], 0, sizeof(MYSQL_BIND));
                result_bind[i].buffer_type = MYSQL_TYPE_STRING;
                result_bind[i].buffer = buffers[i];
                result_bind[i].buffer_length = fields[i].length + 1;
                result_bind[i].length = &lengths[i];
                result_bind[i].is_null = &is_null[i];
            }

            if (mysql_stmt_bind_result(stmt, result_bind.data()) != 0)
            {
                throw std::runtime_error("Bind result failed: " + std::string(mysql_stmt_error(stmt)));
            }

            if (mysql_stmt_store_result(stmt) != 0)
            {
                throw std::runtime_error("Store result failed: " + std::string(mysql_stmt_error(stmt)));
            }

            // 获取数据
            while (mysql_stmt_fetch(stmt) == 0)
            {
                std::vector<std::string> current_row;
                for (unsigned int i = 0; i < num_fields; i++)
                {
                    if (is_null[i])
                    {
                        current_row.push_back("NULL");
                    }
                    else
                    {
                        buffers[i][lengths[i]] = '\0';
                        current_row.push_back(buffers[i]);
                    }
                }
                rows.push_back(current_row);
            }

            // 清理资源
            for (unsigned int i = 0; i < num_fields; i++)
            {
                delete[] buffers[i];
            }
            delete[] is_null;
            mysql_free_result(result);
            mysql_stmt_close(stmt);

            return rows;
        }
        catch (...)
        {
            mysql_stmt_close(stmt);
            throw;
        }
    }

    // 关闭连接
    void close()
    {
        std::lock_guard<std::mutex> lock(mutex_);
        if (mysql_)
        {
            mysql_close(mysql_);
            mysql_ = nullptr;
        }
        connected_ = false;
    }

    // 析构函数
    ~MySQLHelper()
    {
        close();
    }

    // 禁止拷贝构造和赋值
    MySQLHelper(const MySQLHelper &) = delete;
    MySQLHelper &operator=(const MySQLHelper &) = delete;

private:
    MySQLHelper() : mysql_(nullptr), connected_(false) {}

    void checkConnection()
    {
        if (!connected_ || !mysql_)
        {
            throw std::runtime_error("MySQL not connected");
        }
    }

    MYSQL *mysql_;
    bool connected_;
    std::mutex mutex_;
};

// 测试基本连接和查询功能
void testBasicOperations() {
    std::cout << "\n=== 测试基本操作 ===" << std::endl;
    
    try {
        // 获取MySQLHelper实例并初始化连接
        MySQLHelper& db = MySQLHelper::getInstance();
        db.initialize("192.168.100.128", "root", "1", "test", 3306);

        // 创建测试表
        std::cout << "\n创建测试表..." << std::endl;
        db.execute("DROP TABLE IF EXISTS test_users");
        db.execute("CREATE TABLE test_users ("
                  "id INT AUTO_INCREMENT PRIMARY KEY, "
                  "username VARCHAR(50) NOT NULL, "
                  "email VARCHAR(100), "
                  "age INT, "
                  "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");

        // 插入测试数据
        std::cout << "\n插入测试数据..." << std::endl;
        db.execute("INSERT INTO test_users (username, email, age) VALUES "
                  "('张三', 'zhangsan@example.com', 25),"
                  "('李四', 'lisi@example.com', 30),"
                  "('王五', NULL, 35)");

        // 查询数据
        std::cout << "\n查询所有用户：" << std::endl;
        auto result = db.query("SELECT * FROM test_users");
        for (const auto& row : result) {
            for (size_t i = 0; i < row.size(); i++) {
                std::cout << row[i];
                if (i < row.size() - 1) {
                    std::cout << " | ";
                }
            }
            std::cout << std::endl;
        }

    } catch (const std::exception& e) {
        std::cerr << "错误: " << e.what() << std::endl;
    }
}

// 测试预处理语句功能
void testPreparedStatement() {
    std::cout << "\n=== 测试预处理语句 ===" << std::endl;
    
    try {
        MySQLHelper& db = MySQLHelper::getInstance();

        // 准备插入数据
        char username[50] = "赵六";
        char email[100] = "zhaoliu@example.com";
        int age = 28;
        
        MYSQL_BIND bind[3];
        memset(bind, 0, sizeof(bind));

        bind[0].buffer_type = MYSQL_TYPE_STRING;
        bind[0].buffer = username;
        bind[0].buffer_length = strlen(username);

        bind[1].buffer_type = MYSQL_TYPE_STRING;
        bind[1].buffer = email;
        bind[1].buffer_length = strlen(email);

        bind[2].buffer_type = MYSQL_TYPE_LONG;
        bind[2].buffer = &age;

        // 使用预处理语句插入数据
        std::cout << "\n使用预处理语句插入数据..." << std::endl;
        db.executePreparedStatement("INSERT INTO test_users (username, email, age) VALUES (?, ?, ?)", 
                                 bind, 3);

        // 使用预处理语句查询数据
        std::cout << "\n使用预处理语句查询年龄大于25的用户：" << std::endl;
        age = 25;
        MYSQL_BIND query_bind[1];
        memset(query_bind, 0, sizeof(query_bind));
        query_bind[0].buffer_type = MYSQL_TYPE_LONG;
        query_bind[0].buffer = &age;

        auto result = db.queryPreparedStatement("SELECT * FROM test_users WHERE age > ?", 
                                               query_bind, 1);
        
        for (const auto& row : result) {
            for (size_t i = 0; i < row.size(); i++) {
                std::cout << row[i];
                if (i < row.size() - 1) {
                    std::cout << " | ";
                }
            }
            std::cout << std::endl;
        }

    } catch (const std::exception& e) {
        std::cerr << "错误: " << e.what() << std::endl;
    }
}

// 测试错误处理
void testErrorHandling() {
    std::cout << "\n=== 测试错误处理 ===" << std::endl;
    
    try {
        MySQLHelper& db = MySQLHelper::getInstance();

        // 尝试执行错误的SQL语句
        std::cout << "\n尝试执行错误的SQL语句..." << std::endl;
        db.execute("INSERT INTO non_existent_table (id) VALUES (1)");

    } catch (const std::exception& e) {
        std::cout << "捕获到预期错误: " << e.what() << std::endl;
    }
}

// 测试连接状态
void testConnectionStatus() {
    std::cout << "\n=== 测试连接状态 ===" << std::endl;
    
    try {
        MySQLHelper& db = MySQLHelper::getInstance();
        
        // 执行简单查询测试连接
        std::cout << "\n测试连接状态..." << std::endl;
        auto result = db.query("SELECT 1");
        std::cout << "连接状态正常" << std::endl;

    } catch (const std::exception& e) {
        std::cerr << "错误: " << e.what() << std::endl;
    }
}

int main() {
    std::cout << "开始MySQLHelper类功能测试..." << std::endl;

    // 运行所有测试
    testBasicOperations();
    testPreparedStatement();
    testErrorHandling();
    testConnectionStatus();

    std::cout << "\n测试完成" << std::endl;
    return 0;
}

